import os
import configparser
from datetime import datetime
from typing import Dict, List, Tuple, Union, Callable
from playhouse.pool import PooledMySQLDatabase

if __name__ == "__main__":
    # 当作为脚本直接运行时，使用这些导入
    import sys
    from pathlib import Path
    
    # 将项目根目录添加到Python路径
    project_root = str(Path(__file__).parent.parent.absolute())
    if project_root not in sys.path:
        sys.path.insert(0, project_root)
    
    import pandas as pd
    import numpy as np
    from engine.object import BarData
    from engine.constant import OffsetType,AssetType
    from sqlalchemy import create_engine
    from sqlalchemy.pool import QueuePool
    from loguru import logger

else:
    # 当作为模块导入时，使用相对导入
    import pandas as pd
    import numpy as np
    from .object import BarData
    from .constant import OffsetType,AssetType
    from sqlalchemy import create_engine
    from sqlalchemy.pool import QueuePool
    from loguru import logger


def convert_dataframe_to_bar_data(
    data: pd.DataFrame, 
    symbol: str, 
    asset_type: AssetType,
    offset:OffsetType
) -> Dict[datetime, BarData]:
    """
    将DataFrame转换为Dict[datetime, BarData]格式
    
    Args:
        data: 包含OHLCV数据的DataFrame
        symbol: 证券代码
        asset_type: 资产类型，默认为债券
        offset:k线周期
        
    Returns:
        Dict[datetime, BarData]: 转换后的BarData字典
    """
    bar_data_dict = {}
    for date, row in data.iterrows():
        # 创建BarData对象
        bar = BarData(
            timestamp=date,
            symbol=symbol,
            asset_type=asset_type,
            open_price=row.get('open', row.get('close')),
            high_price=row.get('high', row.get('close')),
            low_price=row.get('low', row.get('close')),
            close_price=row['close'],
            volume=row.get('volume', 0),
            avg_price=row.get('avg', row.get('close')),
            open_interest=row.get('open_interest', 0),
            offset=offset
        )
        bar_data_dict[date] = bar
    
    return bar_data_dict


def generate_bond_price_df(
    start_date: Union[str, datetime],
    end_date: Union[str, datetime],
) -> pd.DataFrame:
    """
    生成债券价格随机序列，考虑债券特性和市场因素
    
    Args:
        start_date: 开始日期
        end_date: 结束日期
        coupon_rate: 票面利率，默认3.5%
        maturity_date: 到期日，若为None则设为end_date后10年
        initial_price: 初始价格，默认100.0（面值）
        volatility: 价格波动率，默认0.5%
        mean_reversion_strength: 均值回归强度，控制价格回归到理论价值的速度
        interest_rate_vol: 利率波动率，影响债券价格的随机漂移
        random_seed: 随机数种子，用于复现结果
        include_macro_events: 是否包含宏观经济事件影响
        freq: 价格序列频率，默认为日频('D')
        
    Returns:
        pd.DataFrame: 包含OHLC价格的DataFrame
    """
    date_range = pd.date_range(start=start_date, end=end_date, freq='D')
    
    # 生成随机价格序列，均值为100，标准差为1
    prices = np.random.normal(100, 1, len(date_range))
    
    # 确保价格在合理范围内
    prices = np.clip(prices, 95, 105)
    
    # 创建价格DataFrame (仍然需要创建DataFrame用于可视化和分析)
    data = pd.DataFrame({
        'date': date_range,
        'open': prices,
        'high': prices * 1.005,
        'low': prices * 0.995,
        'close': prices,
        'volume': np.random.randint(1000, 10000, len(date_range))
    })
    data.set_index('date', inplace=True)
    
    return data

def generate_backtest_report(backtest_results, position_data=None, project_root=None, start_date=None, end_date=None):
    """
    生成回测报告HTML
    
    Args:
        backtest_results: 回测结果
        position_data: 持仓占比数据
        project_root: 项目根目录
        start_date: 回测开始日期
        end_date: 回测结束日期
    
    Returns:
        报告文件路径
    """
    # 在函数内部导入来避免循环依赖
    from visualization.performance import PerformanceAnalyzer, PerformanceVisualizer
    
    if project_root is None:
        project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    
    # 确保报告目录存在
    reports_dir = os.path.join(project_root, 'reports')
    os.makedirs(reports_dir, exist_ok=True)
    
    # 提取回测统计数据
    equity_curve = backtest_results['equity_curve']
    trades = backtest_results.get('trades', [])
    
    # 计算回撤
    if 'drawdown' not in equity_curve.columns:
        # 计算高水位线
        equity_curve['high_watermark'] = equity_curve['total_equity'].cummax()
        # 计算回撤百分比
        equity_curve['drawdown'] = (equity_curve['high_watermark'] - equity_curve['total_equity']) / equity_curve['high_watermark']
    
    # 记录实际回测区间
    if start_date and end_date:
        logger.info(f"报告中使用的回测区间: {start_date} 至 {end_date}")
    else:
        # 如果没有提供日期，则使用净值曲线的日期范围
        start_date = equity_curve.index[0]
        end_date = equity_curve.index[-1]
        logger.info(f"报告使用净值曲线的日期范围: {start_date} 至 {end_date}")
    
    # 准备性能分析器所需的数据格式
    # 将equity_curve转换为PerformanceAnalyzer需要的格式
    perf_data = pd.DataFrame({
        'date': equity_curve.index,
        'total_value': equity_curve['total_equity'],
        'returns': equity_curve['returns'] if 'returns' in equity_curve.columns else equity_curve['total_equity'].pct_change().fillna(0),
        'drawdown': equity_curve['drawdown'] if 'drawdown' in equity_curve.columns else equity_curve['drawdown'].copy()
    })
    
    # 添加持仓占比数据
    if position_data:
        # 将持仓数据转换为DataFrame
        position_df = pd.DataFrame(position_data)
        position_df.set_index('date', inplace=True)
        
        # 确保日期索引为datetime类型
        if not isinstance(position_df.index, pd.DatetimeIndex):
            position_df.index = pd.to_datetime(position_df.index)
        
        # 获取所有唯一的债券代码
        all_symbols = set()
        for col in position_df.columns:
            if col != 'weights' and col != 'date':
                all_symbols.add(col)
        
        logger.info(f"报告中的债券代码: {all_symbols}")
        
        # 对齐日期索引
        aligned_dates = equity_curve.index
        weights_data = pd.DataFrame(index=aligned_dates, columns=list(all_symbols))
        
        # 填充缺失日期的数据
        for symbol in all_symbols:
            # 对于每个日期，找到最近的持仓数据
            for date in aligned_dates:
                # 找出该日期之前最近的日期（含当天）
                closest_date = position_df.index[position_df.index <= date]
                if len(closest_date) > 0:
                    closest_date = closest_date[-1]
                    # 直接从position_df获取标的的权重数据
                    if symbol in position_df.columns:
                        # 将百分比转换为小数
                        weights_data.at[date, symbol] = position_df.at[closest_date, symbol] / 100.0
                    else:
                        weights_data.at[date, symbol] = 0.0
                else:
                    weights_data.at[date, symbol] = 0.0
        
        # 将权重数据添加到perf_data
        weights_records = []
        for date, row in weights_data.iterrows():
            weights_records.append(row.to_dict())
        perf_data['weights'] = weights_records
    
    # 初始化性能分析器
    analyzer = PerformanceAnalyzer(perf_data)
    
    # 使用可视化器生成报告
    visualizer = PerformanceVisualizer(analyzer, trades)
    
    # 设置报告的回测区间
    if hasattr(visualizer, 'set_backtest_period') and start_date and end_date:
        # 将日期转换为字符串格式
        start_str = start_date.strftime('%Y-%m-%d') if hasattr(start_date, 'strftime') else str(start_date)
        end_str = end_date.strftime('%Y-%m-%d') if hasattr(end_date, 'strftime') else str(end_date)
        visualizer.set_backtest_period(start_str, end_str)
    
    # 生成报告文件名
    report_filename = f"backtest_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
    report_path = os.path.join(reports_dir, report_filename)
    
    # 生成HTML报告
    visualizer.export_html_report(report_path)
    
    logger.info(f"报告已生成: {report_path}")
    
    return report_path


class MysqlUtility:
    __pool = None

    def __init__(self, dbname="mysql", pool_size=4, 
                        max_overflow=2, pool_timeout=10, 
                        pool_recycle=1800):
        """
        创建MySQL连接池工具类
        
        Args:
            dbname: 配置文件中的数据库部分名称
            pool_size: 连接池大小
            max_overflow: 最大溢出连接数
            pool_timeout: 连接超时时间
            pool_recycle: 连接回收时间
        """
        logger.info("创建数据库连接池")
        try:
            # 读取配置文件
            cf = configparser.ConfigParser()
            cf.read('resource.ini')
            
            # 获取配置信息
            host = cf.get(dbname, dbname + "_" + "host")
            port = cf.getint(dbname, dbname + "_" + "port")
            user = cf.get(dbname, dbname + "_" + "user")
            passwd = cf.get(dbname, dbname + "_" + "password")
            db = cf.get(dbname, dbname + "_" + "database")
            
            # 创建连接引擎
            self.engine = create_engine(
                f"mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}?charset=utf8&autocommit=true",
                pool_size=pool_size,
                max_overflow=max_overflow,
                pool_timeout=pool_timeout,
                pool_recycle=pool_recycle,
                pool_pre_ping=True,
                poolclass=QueuePool,
            ).execution_options(autocommit=True)
            self.conn = self.engine.connect()
            logger.info(f"成功连接到MySQL数据库: {db}")
        except Exception as e:
            logger.error(f"连接MySQL数据库失败: {e}")
            raise

    @staticmethod
    def select(conn, sql):
        """执行SELECT查询，返回单条结果"""
        try:
            logger.info(f"执行SELECT查询: {sql}")
            result = conn.execute(sql)
            return result.fetchone()
        except Exception as e:
            logger.error(f"执行SELECT查询异常: {e}")
            raise

    @staticmethod
    def select_all(conn, sql):
        """执行SELECT查询，返回所有结果"""
        try:
            logger.info(f"执行SELECT ALL查询: {sql}")
            result = conn.execute(sql)
            return result.fetchall()
        except Exception as e:            
            logger.error(f"执行SELECT ALL查询异常: {e}")
            raise

    def dispose(self):
        """关闭连接并释放资源"""
        if hasattr(self, 'conn') and self.conn:
            self.conn.close()
        if hasattr(self, 'engine') and self.engine:
            self.engine.dispose()
        logger.info("数据库连接已关闭")

def virtual(func: Callable) -> Callable:
    """
    mark a function as "virtual", which means that this function can be override.
    any base class should use this or @abstractmethod to decorate all functions
    that can be (re)implemented by subclasses.
    """
    return func

def symbolstr2list(symbol: str) -> List[str]:
    """
    将符号字符串转换为列表
    """
    if "," in symbol:
        return list(set(symbol.split(",")))
    else:
        return [symbol]
def listofstr2symbolstr(symbolist:List[str],sql=False) -> str:
    """
    将字符串列表转换为str
    """
    if sql:
        return '"'+'","'.join(symbolist)+'"'
    else:
        return ",".join(symbolist)

class MysqlPeeweeUtility:
    def __init__(self, dbname="mysql", max_connections=32, stale_timeout=300):
        """
        创建MySQL连接池工具类
        
        Args:
            dbname: 配置文件中的数据库部分名称
            max_connections: 连接池大小
            stale_timeout: 连接超时时间
        """
        logger.info("创建数据库连接池")
        try:
            # 读取配置文件
            cf = configparser.ConfigParser()
            cf.read('resource.ini')
            
            # 获取配置信息
            host = cf.get(dbname, dbname + "_" + "host")
            port = cf.getint(dbname, dbname + "_" + "port")
            user = cf.get(dbname, dbname + "_" + "user")
            passwd = cf.get(dbname, dbname + "_" + "password")
            db = cf.get(dbname, dbname + "_" + "database")
            self.db = PooledMySQLDatabase(
                                    db,
                                    port=port,
                                    user=user,
                                    password=passwd,
                                    host=host,
                                    max_connections=max_connections,
                                    stale_timeout=stale_timeout
                                    )
            
            logger.info(f"成功连接到MySQL数据库: {db}")
        except Exception as e:
            logger.error(f"连接MySQL数据库失败: {e}")
            raise


if __name__ == "__main__":
    # try:
    #     test = MysqlUtility('mysql')
    #     print(f"成功创建数据库连接: {test}")
    #     # 关闭连接
    #     test.dispose()
    # except Exception as e:
    #     print(f"数据库连接测试失败: {e}")
    try:
        test = MysqlPeeweeUtility('mysql')
        print(f"成功创建数据库连接: {test}")
        # 关闭连接
        test.db.close()
        
    except Exception as e:
        print(f"数据库连接测试失败: {e}")